if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RptAgrProjectEndingReport]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[RptAgrProjectEndingReport]

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[RptAgrProjectEndingReport]
(
	@DaysFromProjectEndSelected				int,
	@InstitutionSelected								int = 10--'All'
)
 AS

-- =======================================================================
-- Procedure Name: dbo.RptAgrProjectEndingReport
--
-- Author:      Arthur A. Bianchini III
--
-- Create date: 4/25/2008
--
-- Description: Source of Report, "Proposal Project Ending Report"
--                  In Insight DB Reporting Services
--
--Purpose-  --Select Projects Ending in 30, 60, 90 days 
--
-- Parameters:    
--                     
--     @DaysFromProjectEndSelected	                 
--     @Institution       
-- =======================================================================

BEGIN

DECLARE @RespInstitutionId int

IF @InstitutionSelected = 11--'All - excluding DFCI'
	SET @RespInstitutionID = 10--'All'
ELSE
	SET @RespInstitutionID = @InstitutionSelected

	CREATE TABLE #RptAgrProjectEnding

	(RespInstitution										varchar(32) NULL, 
	 InstitutionID											INT NULL,
     ProposalNumber									varchar(30) NULL, 
	 PIName												varchar(100) NULL,  
	 ChiefCode											varchar(32) NULL, 
	 Sponsor												varchar(150) NULL,
	 SponsorID											varchar(32) NULL, 
	 ProposalStatus										varchar(80) NULL,
	 ProjectEnd											datetime NULL,
	 PostAwardManager								varchar(100) NULL, 
	 DaysFromProjectEndSelected				int NULL,
	 InstitutionSelected									int NULL
	)

INSERT INTO #RptAgrProjectEnding

SELECT DISTINCT
ei.Description as RespInstitution,
ei.InstitutionId,
p.inst_no as ProposalNumber, 
f.lname + ', ' + f.fname as PIName, 
u.int_unit_code as ChiefCode, 
sp.spon_name as Sponsor, 
spon_awd as SponsorID, 
cc.code_desc as ProposalStatus, 
p.app_end_dt as ProjectEnd, 
fpa.lname + ', ' + fpa.fname,
@DaysFromProjectEndSelected,
@InstitutionSelected
FROM proposal p
JOIN  codetab pt 
	on pt.code = p.prop_type 
	and pt.arr_name = 'prop_type' 
	and pt.code_desc = 'Master'
JOIN codetab cc 
	on cc.arr_name = 'prop_stat' 
	and p.prop_stat = cc.codeID 
	and cc.code_desc not like 'Closed%' 
LEFT JOIN proppds o 
	ON o.prop_no = p.prop_no 
	and o.inst_code = p.inst_code 
	and o.first_pd = 1
LEFT JOIN faculty f 
	ON o.unique_id = f.unique_id
LEFT JOIN unit u 
	ON u.unit_code = p.unit_code 
	and u.inst_code = 'partners'
JOIN	Unit un 
	ON		p.inst_code = un.inst_code
	AND		p.unit_code = un.unit_code
JOIN Unit as inst
	ON    inst.inst_code = un.inst_code
	AND  left(un.deptid,4) = inst.deptID
	AND  len(inst.deptID) = 4
	AND inst.int_unit_code in ('1200','1400','1700','2200','1400','FH','NWH','DFCI','DFPCC')
JOIN		InfoEdInstitutions ei
	ON	ei.int_unit_code = inst.int_unit_code
LEFT JOIN unit i 
	ON len(i.deptID) = 4 
	and i.deptId = left(u.deptId,4) 
	and i.int_unit_code is not null
LEFT JOIN sponspas sp 
	ON sp.spon_code = p.spon_code
LEFT JOIN prop_u pa 
	ON pa.prop_no = p.prop_no 
	and pa.inst_code = p.inst_code
LEFT JOIN faculty fpa 
	on pa.p_chr_2 = fpa.unique_id
WHERE	p.app_end_dt between getdate() and CONVERT(INT,(dateadd(dd,@DaysFromProjectEndSelected,getdate()) ))

IF @InstitutionSelected = 11--'All - excluding DFCI'
   BEGIN
	DELETE FROM #RptAgrProjectEnding
	WHERE RespInstitution IN ('DFCI', 'DFPCC')
   END

	SELECT DISTINCT
	 RespInstitution as Institution, 
     ProposalNumber, 
	 PIName,  
	 ChiefCode, 
	 Sponsor,
	 SponsorID, 
	 ProposalStatus,
	 ProjectEnd,
	 PostAwardManager, 
	 DaysFromProjectEndSelected,
	 CASE InstitutionSelected	
		WHEN	1	THEN	'BWH'
		WHEN	2	THEN	'DFCI'
		WHEN	3	THEN	'DFPCC'
		WHEN	4	THEN	'FH'
		WHEN	5	THEN	'IHP'
		WHEN	6	THEN	'MCL'
		WHEN	7	THEN	'MGH'
		WHEN	8	THEN	'NWH'
		WHEN	9	THEN	'SRH'
		WHEN	10	THEN	'All'
		WHEN	11	THEN	'All - excluding DFCI'
END	as	InstitutionSelected	
	 FROM #RptAgrProjectEnding
	 WHERE  (@RespInstitutionID = 10 OR Institutionid = @RespInstitutionID)
	ORDER BY RespInstitution,ProposalNumber

END
GO


